package com.jietu.mapper;

import com.jietu.entity.Vo;
import com.jietu.entity.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author LQ&LYPlq
 * @ClassName VoMapper
 * @description: TODO
 * @date 2024年12月11日
 * @version: 1.0
 */
@Mapper
public interface VoMapper {
    @Select(" select  bp.name,sum(oi.final_total_amount) `value`  from  order_info oi \n" +
            " left join base_province bp on oi.province_id = bp.id \n" +
            "  group by bp.name ")
    List<Vo> getList1();

    /**
     *按品牌展示销售排行
     * @return
     */
    @Select("select  bt.name,sum(oi.final_total_amount) `value`  from  order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "group by  bt.name order by `value`   desc ")
    List<Vo> getList2();

    /**
     * 按各品类展示销售占比
     * @return
     */
 /*   @Select(" select  bc1.name,sum(oi.final_total_amount) value  from  order_info oi\n" +
            "            left join order_detail od on oi.id = od.order_id\n" +
            "            left join sku_info si on si.id = od.sku_id\n" +
            "            left join base_category3 b on si.category3_id = b.id\n" +
            "            left join base_category2 c on c.id = b.category2_id\n" +
            "            left join base_category1 bc1 on bc1.id = c.category1_id \n" +
            "            group by bc1.name  ")*/
    @Select("select     b1.`name`,  ifnull( sum(oi.final_total_amount),0) value        from   base_category1   b1   \n" +
            "         left  join   base_category2    b2 on   b1.id=b2.category1_id\n" +
            "\t\t\t\t left  join   base_category3   b3  on   b2.id=b3.category2_id\n" +
            "\t\t\t\t left  join   sku_info    skf   on    b3.id=skf.category3_id\n" +
            "\t\t\t\t left  join   order_detail   odt   on  skf.id=odt.sku_id\n" +
            "\t\t\t\t left  join   order_info   oi   on   odt.order_id=oi.id  \n" +
            "\t\t\t\t  group  by  b1.`name`")
     List<Vo> getList3();
    /**
     * 新老用户占比
     * @return
     */
    @Select("select '用户数(人)' type,a.newNum,b.oldNum from\n" +
            "            (select  count(*) newNum  from user_info  where timestampdiff(year,create_time,now())<1) a,\n" +
            "            (select  count(*) oldNum  from user_info  where timestampdiff(year,create_time,now())>=1) b")
    List<Vo1> getList4();
}
